﻿using MetaQuotes.MT5WebAPI.Common;
using MT5Collect;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace MT5DataCollect
{
    public class DBService
    {
        #region 数据同步

        public static string creator = "system";

        /// <summary>
        /// 读取MT5服务器
        /// </summary>
        /// <returns></returns>
        public static DataTable GetMt5Server()
        {
            try
            {
                string sql = "select * from mt5_server";
                DataTable dt = DBHelper.ExecuteQuery(sql);
                return dt;
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 更新用户数据到DB
        /// </summary>
        /// <param name="mtUser"></param>
        /// <param name="server"></param>
        /// <param name="group"></param>
        /// <returns></returns>
        public static int SaveUser(MTUser mtUser, string server, string group)
        {
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            try
            {
                int count = 0;
                string sql = $"select * from mt5_user where login={mtUser.Login}";
                mtUser.Group = mtUser.Group.Replace("\\", "\\\\\\\\");
                DataTable dt = DBHelper.ExecuteQuery(sql);
                if (dt.Rows.Count == 0)
                {
                    sql = $"insert into mt5_user (login,type,`server`,crm_id,`group`,certSerialNumber,rights,registration,lastAccess,lastIP," +
                        $"name,company,account,country,`language`,city,state,zipcode,address," +
                        $"phone,email,id,mqid,clinetid,`status`,`comment`,color,phonePassword,lastPassChange," +
                        $"passwordHash,otpsecret,laverage,leadSource,leadCampaign,interstrate,commissionDaily,commissionMonthly,commissionAgentDaily,commissionAgentMonthly," +
                        $"agent,balance,balancePrevDay,balancePrevMonth,equityPrevDay,equityPrevMonth,credit,limitOrders,limitPositionsValue,creator,create_date) values" +
                        $"({mtUser.Login},0,'{server}',0,'{mtUser.Group}',{mtUser.CertSerialNumber},{mtUser.Rights},{mtUser.Registration},{mtUser.LastAccess},'{mtUser.LastIP}'," +
                        $"'{mtUser.Name}','{mtUser.Company}','{mtUser.Account}','{mtUser.Country}',{mtUser.Language},'{mtUser.City}','{mtUser.State}','{mtUser.ZIPCode}','{mtUser.Address}'," +
                        $"'{mtUser.Phone}','{mtUser.Email}','{mtUser.ID}',0,0,'{mtUser.Status}','{mtUser.Comment}',{mtUser.Color},'{mtUser.PhonePassword}',{mtUser.LastPassChange}" +
                        $",'','',{mtUser.Leverage},'','',{mtUser.InterestRate},{mtUser.CommissionDaily},{mtUser.CommissionMonthly},{mtUser.CommissionAgentDaily},{mtUser.CommissionAgentMonthly}," +
                        $"{mtUser.Agent},{mtUser.Balance},{mtUser.BalancePrevDay},{mtUser.BalancePrevMonth},{mtUser.EquityPrevDay},{mtUser.EquityPrevMonth},{mtUser.Credit},'','','{creator}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')";
                    count = DBHelper.ExecuteNonQuery(sql);
                    string cimId = dt.Rows[0]["crm_id"].ToString();
                    sql = $"select * from crm_user where id='{cimId}'";
                    DataTable dt_Crm = DBHelper.ExecuteQuery(sql);
                    if (dt_Crm.Rows.Count > 0)
                    {
                        sql = $"update crm_user set name='{mtUser.Name}' where id={cimId}";
                        count = DBHelper.ExecuteNonQuery(sql);
                    }
                }
                else
                {
                    sql = $"update mt5_user set `group`='{mtUser.Group}',certSerialNumber={mtUser.CertSerialNumber},rights='{mtUser.Rights}',registration='{mtUser.Registration}',lastAccess='{mtUser.LastAccess}',lastIP='{mtUser.LastIP}'," +
                        $"name='{mtUser.Name}',company='{mtUser.Company}',account='{mtUser.Account}',country='{mtUser.Country}',`language`={mtUser.Language},city='{mtUser.City}',state='{mtUser.State}',zipcode='{mtUser.ZIPCode}',address='{mtUser.Address}'," +
                        $"phone='{mtUser.Phone}',email='{mtUser.Email}',id='{mtUser.ID}',`status`='{mtUser.Status}',`comment`='{mtUser.Comment}',color={mtUser.Color},phonePassword='{mtUser.PhonePassword}',lastPassChange={mtUser.LastPassChange}," +
                        $"laverage={mtUser.Leverage},interstrate={mtUser.InterestRate},commissionDaily={mtUser.CommissionDaily},commissionMonthly={mtUser.CommissionMonthly},commissionAgentDaily={mtUser.CommissionAgentDaily},commissionAgentMonthly={mtUser.CommissionAgentMonthly}," +
                        $"agent={mtUser.Agent},balance={mtUser.Balance},balancePrevDay={mtUser.BalancePrevDay},balancePrevMonth={mtUser.BalancePrevMonth},equityPrevDay={mtUser.EquityPrevDay},equityPrevMonth={mtUser.EquityPrevMonth},credit={mtUser.Credit}, update_date ='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'" +
                        $" where login={mtUser.Login}";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                return count;
            }
            catch (Exception ex)
            {
                throw;
            }
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
        }

        /// <summary>
        /// 更新用户数据到DB
        /// </summary>
        /// <param name="mtUser"></param>
        /// <param name="server"></param>
        /// <param name="group"></param>
        /// <returns></returns>
        public static int SaveAccount(MTAccount mTAccount)
        {
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            try
            {
                int count = 0;
                string sql = $"select * from mt5_user where login={mTAccount.Login}";
                DataTable dt = DBHelper.ExecuteQuery(sql);
                if (dt.Rows.Count == 1)
                {
                    sql = $"update mt5_user set credit={mTAccount.Credit},equity={mTAccount.Equity},floating={mTAccount.Floating}," +
                        $"margin={mTAccount.Margin},marginFree={mTAccount.MarginFree},marginLevel={mTAccount.MarginLevel},profit={mTAccount.Profit},storage={mTAccount.Storage}, update_date ='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'" +
                        $" where login={mTAccount.Login}";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                return count;
            }
            catch (Exception ex)
            {
                throw;
            }
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
        }

        /// <summary>
        /// 更新持仓数据到DB
        /// </summary>
        /// <param name="mTPositions"></param>
        /// <returns></returns>
        public static int SavePosition(MTPosition mTPositions)
        {
            string sql = "";
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            try
            {
                int count = 0;
                sql = $"select * from mt5_position where position={mTPositions.ExpertPositionID}";
                DataTable dt = DBHelper.ExecuteQuery(sql);
                if (dt.Rows.Count == 0)
                {
                    sql = $"insert into mt5_position (login,symbol,action,enPositionAction,digits,digitsCurrency,contractSize,position," +
                        $"timeCreate,timeUpdate,priceOpen,priceCurrent,priceSL,priceTP,volume,profit,storage,rateProfit" +
                        $",rateMargin,expertID,expertPositionID,comment,activationMode,activationTime,activationPrice,activationFlags,creator,create_date) values" +
                        $"({mTPositions.Login},'{mTPositions.Symbol.ToLower()}',{(int)mTPositions.Action},'{((int)mTPositions.Action == 0 ? "buy" : "sell")}',{mTPositions.Digits},{mTPositions.DigitsCurrency},{mTPositions.ContractSize},{mTPositions.ExpertPositionID}," +
                        $"{mTPositions.TimeCreate},{mTPositions.TimeUpdate},{mTPositions.PriceOpen},{mTPositions.PriceCurrent},{mTPositions.PriceSL},{mTPositions.PriceTP},{mTPositions.Volume},{mTPositions.Profit},{mTPositions.Storage},{mTPositions.RateProfit}" +
                        $",{mTPositions.RateMargin},{mTPositions.ExpertID},{mTPositions.ExpertPositionID},'{mTPositions.Comment}',{mTPositions.ActivationMode},{mTPositions.ActivationTime},{mTPositions.ActivationPrice},{mTPositions.ActivationFlags},'{creator}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                else
                {
                    sql = $"update mt5_position set login={mTPositions.Login},symbol='{mTPositions.Symbol.ToLower()}',action={(int)mTPositions.Action},enPositionAction='{((int)mTPositions.Action == 0 ? "buy" : "sell")}',digits={mTPositions.Digits},digitsCurrency={mTPositions.DigitsCurrency},contractSize={mTPositions.ContractSize},position={mTPositions.ExpertPositionID}," +
                        $"timeCreate={mTPositions.TimeCreate}, timeUpdate={mTPositions.TimeUpdate}, priceOpen={mTPositions.PriceOpen}, priceCurrent={mTPositions.PriceCurrent}, priceSL={mTPositions.PriceSL}, priceTP={mTPositions.PriceTP}, volume={mTPositions.Volume}, profit={mTPositions.Profit}, storage={mTPositions.Storage}, rateProfit={mTPositions.RateProfit}," +
                        $"rateMargin={mTPositions.RateMargin}, expertID={mTPositions.ExpertID}, expertPositionID={mTPositions.ExpertPositionID}, comment='{mTPositions.Comment}', activationMode={mTPositions.ActivationMode}, activationTime={mTPositions.ActivationTime}, activationPrice={mTPositions.ActivationPrice}, activationFlags={mTPositions.ActivationFlags}, update_date ='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'" +
                        $"where position={mTPositions.ExpertPositionID}";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                return count;
            }
            catch (Exception ex)
            {
                throw;
            }
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
        }

        /// <summary>
        /// 清楚持仓数据
        /// </summary>
        /// <param name="mTPositions"></param>
        /// <returns></returns>
        public static int ClearPosition(List<ulong> positions, int login)
        {
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            try
            {
                if (positions.Count > 0)
                {
                    string where = "";
                    foreach (ulong position in positions)
                    {
                        where += $"{position},";
                    }
                    where = where.Substring(0, where.Length - 1);
                    string sql = $"delete from mt5_position where position not in({where}) and login={login}";
                    int count = DBHelper.ExecuteNonQuery(sql);
                    return count;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
        }

        /// <summary>
        /// 清楚持仓数据
        /// </summary>
        /// <param name="mTPositions"></param>
        /// <returns></returns>
        public static int ClearPosition()
        {
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            try
            {
                string sql = $"delete from mt5_position";
                int count = DBHelper.ExecuteNonQuery(sql);
                return count;
            }
            catch (Exception ex)
            {
                throw;
            }
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
        }

        /// <summary>
        /// 更新持仓数据到DB
        /// </summary>
        /// <param name="mTPositions"></param>
        /// <returns></returns>
        public static int SaveDeal(MTDeal mTDeal)
        {
            string sql = "";
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            try
            {
                int count = 0;
                sql = $"select * from mt5_deal where deal={mTDeal.Deal}";
                DataTable dt = DBHelper.ExecuteQuery(sql);
                if (dt.Rows.Count == 0)
                {
                    sql = $"insert into mt5_deal (deal,externalID,login,dealer,`order`,action,enDealAction,entry,enEntryFlags,digits,digitsCurrency,contractSize," +
                        $"time,symbol,price,volume,profit,storage,commission,commissionAgent,rateProfit,rateMargin," +
                        $"expertID,expertPositionID,comment,profitRaw,pricePosition,volumeClosed,tickValue,tickSize,flags,enDealFlags,creator,create_date) values" +
                        $"({mTDeal.Deal},'{mTDeal.ExternalID}',{mTDeal.Login},{mTDeal.Dealer},{mTDeal.Order},{(uint)mTDeal.Action},'{EnDealAction((uint)mTDeal.Action)}',{(uint)mTDeal.Entry},'{EnEntryFlags((uint)mTDeal.Entry)}',{mTDeal.Digits},{mTDeal.DigitsCurrency},{mTDeal.ContractSize}." +
                        $",{mTDeal.Time},'{mTDeal.Symbol.ToLower()}',{mTDeal.Price},{mTDeal.Volume},{mTDeal.Profit},{mTDeal.Storage},{mTDeal.Commission},{mTDeal.CommissionAgent},{mTDeal.RateProfit},{mTDeal.RateMargin}," +
                        $"{mTDeal.ExpertID},{mTDeal.ExpertPositionID},'{mTDeal.Comment}',{mTDeal.ProfitRaw},{mTDeal.PricePosition},{mTDeal.VolumeClosed},{mTDeal.TickValue},{mTDeal.TickSize},{mTDeal.Flags},'','{creator}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                else
                {
                    sql = $"update mt5_deal set externalID='{mTDeal.ExternalID}',login={mTDeal.Login},dealer={mTDeal.Dealer},`order`={mTDeal.Order},action={(uint)mTDeal.Action},enDealAction='{EnDealAction((uint)mTDeal.Action)}',entry={(uint)mTDeal.Entry},enEntryFlags='{EnEntryFlags((uint)mTDeal.Entry)}',digitsCurrency={mTDeal.Digits},digitsCurrency={mTDeal.DigitsCurrency},contractSize={mTDeal.ContractSize}," +
                        $"time={mTDeal.Time},symbol='{mTDeal.Symbol.ToLower()}',price={mTDeal.Price},volume={mTDeal.Volume},profit={mTDeal.Profit},storage={mTDeal.Storage},commission={mTDeal.Commission},commissionAgent={mTDeal.CommissionAgent},rateProfit={mTDeal.RateProfit},rateMargin={mTDeal.RateMargin}," +
                        $"expertID={mTDeal.ExpertID},expertPositionID={mTDeal.ExpertPositionID},comment='{mTDeal.Comment}',profitRaw={mTDeal.ProfitRaw},pricePosition={mTDeal.PricePosition},volumeClosed={mTDeal.VolumeClosed},tickValue={mTDeal.TickValue},tickSize={mTDeal.TickSize},flags={mTDeal.TickSize},update_date ='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}' " +
                        $"where deal={mTDeal.Deal}";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                return count;
            }
            catch (Exception ex)
            {

                throw;
            }
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
        }

        /// <summary>
        /// 清楚持仓数据
        /// </summary>
        /// <param name="mTPositions"></param>
        /// <returns></returns>
        public static int ClearDeal(List<ulong> deals, int login)
        {
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            try
            {
                if (deals.Count > 0)
                {
                    string where = "";
                    foreach (ulong deal in deals)
                    {
                        where += $"{deal},";
                    }
                    where = where.Substring(0, where.Length - 1);
                    string sql = $"delete from mt5_deal where deal not in({where}) and login={login}";
                    int count = DBHelper.ExecuteNonQuery(sql);
                    return count;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
        }

        public static string EnDealAction(uint action)
        {
            switch (action)
            {
                case 0:
                    return "buy";
                case 1:
                    return "sell";
                case 2:
                    return "deposit operation";
                case 3:
                    return "credit operation";
                case 4:
                    return "additional charges";
                case 5:
                    return "correction deals";
                case 6:
                    return "bouns";
                case 7:
                    return "commission";
                case 8:
                    return "daily commission";
                case 9:
                    return "monthly commission";
                case 10:
                    return "daily agent commission";
                case 11:
                    return "monthly agent commission";
                case 12:
                    return "interest rate charges";
                default:
                    return "";
            }
        }

        public static string EnEntryFlags(uint flag)
        {
            switch (flag)
            {
                case 0:
                    return "in market";
                case 1:
                    return "out of market";
                case 2:
                    return "reverse";
                default:
                    return "";
            }
        }

        /// <summary>
        /// 更新持仓数据到DB
        /// </summary>
        /// <param name="mTPositions"></param>
        /// <returns></returns>
        public static int SaveOrder(MTOrder mTOrder)
        {
            string sql = "";
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            try
            {
                int count = 0;
                sql = $"select * from mt5_order where `order`={mTOrder.Order}";
                DataTable dt = DBHelper.ExecuteQuery(sql);
                if (dt.Rows.Count == 0)
                {
                    sql = $"insert into mt5_order (`order`,externalID,login,dealer,symbol,digits,digitsCurrency,contractSize,state,enOrderState," +
                        $"reason,timeSetup,timeExpiration,timeDone,type,enOrderType,typeFill,enOrderFilling,typeTime,enOrderTime," +
                        $"priceOrder,priceTrigger,priceCurrent,priceSL,priceTP,volumeInitial,volumeCurrent,expertID,expertPositionID," +
                        $"comment,activationMode,activationTime,activationPrice,activationFlags,creator,create_date) values" +
                        $"({mTOrder.Order},'{mTOrder.ExternalID}',{mTOrder.Login},{mTOrder.Dealer},'{mTOrder.Symbol.ToLower()}',{mTOrder.Digits},{mTOrder.DigitsCurrency},{mTOrder.ContractSize},{(uint)mTOrder.State},'{enOrderState((uint)mTOrder.State)}'," +
                        $"'{EnOrderReason((uint)mTOrder.Reason)}',{mTOrder.TimeSetup},{mTOrder.TimeExpiration},{mTOrder.TimeDone},{(uint)mTOrder.Type},'{EnOrderType((uint)mTOrder.Type)}',{(uint)mTOrder.TypeFill},'{EnOrderFilling((uint)mTOrder.TypeFill)}',{(uint)mTOrder.TypeTime},'{EnOrderTime((uint)mTOrder.TypeTime)}'," +
                        $"{mTOrder.PriceOrder},{mTOrder.PriceTrigger},{mTOrder.PriceCurrent},{mTOrder.PriceSL},{mTOrder.PriceTP},{mTOrder.VolumeInitial},{mTOrder.VolumeCurrent},{mTOrder.ExpertID},{mTOrder.ExpertPositionID}," +
                        $"'{mTOrder.Comment}',{mTOrder.ActivationMode},{mTOrder.ActivationTime},{mTOrder.ActivationPrice},{mTOrder.ActivationFlags},'{creator}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                else
                {
                    sql = $"update mt5_order set externalID='{mTOrder.ExternalID}',login={mTOrder.Login},dealer={mTOrder.Dealer},symbol='{mTOrder.Symbol.ToLower()}',digits={mTOrder.Digits},digitsCurrency='{mTOrder.DigitsCurrency}',contractSize={mTOrder.ContractSize},state={(uint)mTOrder.State},enOrderState='{enOrderState((uint)mTOrder.State)}'," +
                        $"reason='{EnOrderReason((uint)mTOrder.Reason)}',timeSetup={mTOrder.TimeSetup},timeExpiration={mTOrder.TimeExpiration},timeDone={mTOrder.TimeDone},type={(uint)mTOrder.Type},enOrderType='{EnOrderType((uint)mTOrder.Type)}',typeFill={(uint)mTOrder.TypeFill},enOrderFilling='{EnOrderFilling((uint)mTOrder.TypeFill)}',typeTime={(uint)mTOrder.TypeTime},enOrderTime='{EnOrderTime((uint)mTOrder.TypeTime)}'," +
                        $"priceOrder={mTOrder.PriceOrder},priceTrigger={mTOrder.PriceTrigger},priceCurrent={mTOrder.PriceCurrent},priceSL={mTOrder.PriceSL},priceTP={mTOrder.PriceTP},volumeInitial={mTOrder.VolumeInitial},volumeCurrent={mTOrder.VolumeCurrent},expertID={mTOrder.ExpertID},expertPositionID={mTOrder.ExpertPositionID}," +
                        $"comment='{mTOrder.Comment}',activationMode={mTOrder.ActivationMode},activationTime={mTOrder.ActivationTime},activationPrice={mTOrder.ActivationPrice},activationFlags={mTOrder.ActivationFlags},update_date='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'" +
                        $" where `order`={mTOrder.Order}";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                return count;
            }
            catch (Exception ex)
            {
                throw;
            }
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
        }

        /// <summary>
        /// 清楚持仓数据
        /// </summary>
        /// <param name="mTPositions"></param>
        /// <returns></returns>
        public static int ClearOrder(List<ulong> orders, int login)
        {
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            try
            {
                if (orders.Count > 0)
                {
                    string where = "";
                    foreach (ulong order in orders)
                    {
                        where += $"{order},";
                    }
                    where = where.Substring(0, where.Length - 1);
                    string sql = $"delete from mt5_order where `order` not in({where}) and login={login}";
                    int count = DBHelper.ExecuteNonQuery(sql);
                    return count;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw;
            }
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
        }

        /// <summary>
        /// 清楚持仓数据
        /// </summary>
        /// <param name="mTPositions"></param>
        /// <returns></returns>
        public static int ClearOrder()
        {
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            try
            {
                string sql = $"delete from mt5_order";
                int count = DBHelper.ExecuteNonQuery(sql);
                return count;
            }
            catch (Exception ex)
            {
                throw;
            }
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
        }

        public static string enOrderState(uint state)
        {
            switch (state)
            {
                case 0:
                    return " order started";
                case 1:
                    return "order placed in system";
                case 2:
                    return "order canceled by client";
                case 3:
                    return "order partially filled";
                case 4:
                    return "order filled";
                case 5:
                    return "order rejected";
                case 6:
                    return "order expired";
                default:
                    return "";
            }
        }

        public static string EnOrderReason(uint reason)
        {
            switch (reason)
            {
                case 0:
                    return "order placed manually";
                case 1:
                    return "order placed by expert";
                case 2:
                    return "order placed by dealer";
                case 3:
                    return "order placed due SL";
                case 4:
                    return "order placed due TP";
                case 5:
                    return " order placed due Stop-Out";
                case 6:
                    return "order placed due rollover";
                case 7:
                    return "order placed from external system";
                default:
                    return "";
            }
        }

        public static string EnOrderType(uint type)
        {
            switch (type)
            {
                case 0:
                    return "buy order";
                case 1:
                    return "sell order";
                case 2:
                    return "buy limit order";
                case 3:
                    return "sell limit order";
                case 4:
                    return "buy stop order";
                case 5:
                    return "sell stop order";
                case 6:
                    return "buy stop limit order";
                case 7:
                    return "sell stop limit order";
                default:
                    return "";
            }
        }

        public static string EnOrderFilling(uint typeFill)
        {
            switch (typeFill)
            {
                case 0:
                    return "fill or kill";
                case 1:
                    return "immediate or cancel";
                case 2:
                    return "return order in queue";
                default:
                    return "";
            }
        }

        public static string EnOrderTime(uint typeTime)
        {
            switch (typeTime)
            {
                case 0:
                    return "good till cancel";
                case 1:
                    return "good till day";
                case 2:
                    return "good till specified";
                case 3:
                    return "good till specified day";
                default:
                    return "";
            }
        }

        /// <summary>
        /// 更新持仓数据到DB
        /// </summary>
        /// <param name="mTPositions"></param>
        /// <returns></returns>
        public static int SaveSymbol(MTConSymbol mTConSymbol)
        {
            string sql = "";
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            try
            {
                int count = 0;
                sql = $"select * from symbol where symbol='{mTConSymbol.Symbol}'";
                DataTable dt = DBHelper.ExecuteQuery(sql);
                mTConSymbol.Description = "";
                if (dt.Rows.Count == 0)
                {
                    sql = $"insert into symbol (symbol,description,path,creator,create_date) values" +
                        $"('{mTConSymbol.Symbol.ToLower()}','{mTConSymbol.Description}','{mTConSymbol.Path}','{creator}','{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}')";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                else
                {
                    sql = $"update symbol set description='{mTConSymbol.Description}',path='{mTConSymbol.Path}',update_date ='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}' " +
                        $"where symbol='{mTConSymbol.Symbol.ToLower()}'";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                return count;
            }
            catch (Exception ex)
            {

                throw;
            }
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
        }

        #endregion

        #region 返佣统计
        //public static int DelCommissionDetailedReport(string date)
        //{
        //    try
        //    {
        //        string sql = $"delete from commission_detailed_report where date='{date}'";
        //        int count = DBHelper.ExecuteNonQuery(sql);
        //        return count;
        //    }
        //    catch (Exception)
        //    {

        //        throw;
        //    }
        //}

        public static object lockObject = new object();
        public static void CommissionDetailedReport(CommissionDetailedReportMode model)
        {
            try
            {
                
                lock (lockObject)
                {
                    int count = 0;
                    string sql = $"select * from commission_detailed_report where owner={model.owner} and crmid={model.crmid} and deal={model.deal}";
                    DataTable dt = DBHelper.ExecuteQuery(sql);
                    if (dt.Rows.Count == 0)
                    {
                        //sql = $"insert into commission_detailed_report(owner,crmid,crmlogin,mt5login,deal,symbol,volume,scheme_id,level_id,coefficient,money,date,addtime,starttime,endtime,isreturn,order_id,scheme,level) values (" +
                        //    $"{model.owner},{model.crmid},'{model.crmlogin}',{model.mt5login},{model.deal},'{model.symbol}',{model.volume},{model.scheme_id},{model.level_id},{model.coefficient},{model.money},'{model.date}','{DateTime.Now}','{model.starttime}','{model.endtime}',{model.isreturn},'{model.order_id}','{model.scheme}','{model.level}')";
                        //count = DBHelper.ExecuteNonQuery(sql);
                    }
                    else
                    {
                        sql = $"update commission_detailed_report set owner={model.owner},crmid={model.crmid},crmlogin='{model.crmlogin}',mt5login={model.mt5login},symbol='{model.symbol}',volume={model.volume},scheme_id={model.scheme_id},level_id={model.level_id},coefficient={model.coefficient},money={model.money},date='{model.date}',order_id='{model.order_id}',scheme='{model.scheme}',level='{model.level}'" +
                         $" where deal={model.deal}";
                        count = DBHelper.ExecuteNonQuery(sql);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

        public class CommissionDetailedReportMode
        {
            public int owner { get; set; }
            public int crmid { get; set; }
            public string crmlogin { get; set; }
            public long mt5login { get; set; }
            public long deal { get; set; }
            public string symbol { get; set; }
            public long volume { get; set; }
            public int scheme_id { get; set; }
            public int level_id { get; set; }
            public decimal coefficient { get; set; }
            public decimal money { get; set; }
            public string date { get; set; }
            public string starttime { get; set; }
            public string endtime { get; set; }
            public int isreturn { get; set; }
            public string order_id { get; set; }
            public string scheme { get; set; }
            public string level { get; set; }
        }

        public static int DelDailyDetailedReport(string date)
        {
            try
            {
                string sql = $"delete from daily_detailed_report where date='{date}'";
                int count = DBHelper.ExecuteNonQuery(sql);
                return count;
            }
            catch (Exception)
            {

                throw;
            }
        }


        public static void DailyDetailedReport(DailyDetailedReportMode model)
        {
            try
            {
                int count = 0;
                string sql = $"select * from daily_detailed_report where owner={model.owner} and symbol='{model.symbol}' and date='{model.date}' and scheme_id={model.scheme_id} and level_id={model.level_id} and coefficient={model.coefficient}";
                DataTable dt = DBHelper.ExecuteQuery(sql);
                if (dt.Rows.Count == 0)
                {
                    sql = $"insert into daily_detailed_report(owner,symbol,volume,scheme_id,level_id,coefficient,money,date,addtime,starttime,endtime,scheme,level) values (" +
                        $"{model.owner},'{model.symbol}',{model.volume},{model.scheme_id},{model.level_id},{model.coefficient},{model.money},'{model.date}','{DateTime.Now}','{model.starttime}','{model.endtime}','{model.scheme}','{model.level}')";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                //else
                //{
                //    sql = $"update daily_detailed_report set volume={model.volume},money={model.money}" +
                //     $" where owner={model.owner} and symbol='{model.symbol}' and date='{model.date}' and scheme_id={model.scheme_id} and level_id={model.level_id} and coefficient={model.coefficient}";
                //    count = DBHelper.ExecuteNonQuery(sql);
                //}
            }
            catch (Exception)
            {

                throw;
            }
        }

        public static int DelPersonReport(string date)
        {
            try
            {
                string sql = $"delete from person_report where date='{date}'";
                int count = DBHelper.ExecuteNonQuery(sql);
                return count;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public class DailyDetailedReportMode
        {
            public int owner { get; set; }
            public string symbol { get; set; }
            public long volume { get; set; }
            public int scheme_id { get; set; }
            public int level_id { get; set; }
            public decimal coefficient { get; set; }
            public decimal money { get; set; }
            public string date { get; set; }
            public string starttime { get; set; }
            public string endtime { get; set; }
            public string scheme { get; set; }
            public string level { get; set; }
        }

        public static void PersonReport(PersonReportMode model)
        {
            try
            {
                int count = 0;
                string sql = $"select * from person_report where owner={model.owner} and crmid={model.crmid} and scheme_id={model.scheme_id} and level_id={model.level_id} and date={model.date}";
                DataTable dt = DBHelper.ExecuteQuery(sql);
                if (dt.Rows.Count == 0)
                {
                    sql = $"insert into person_report(owner,crmid,crmlogin,mt5login,scheme_id,coefficient,money,level_id,date,addtime,starttime,endtime,scheme,level) values (" +
                        $"{model.owner},{model.crmid},'{model.crmlogin}',{model.mt5login},{model.scheme_id},{model.coefficient},{model.money},{model.level_id},'{model.date}','{DateTime.Now}','{model.starttime}','{model.endtime}','{model.scheme}','{model.level}')";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                //else
                //{
                //    sql = $"update person_report set money={model.money}" +
                //     $" where owner={model.owner} and crmid={model.crmid} and scheme_id={model.scheme_id} and level_id={model.level_id} and date={model.date}";
                //    count = DBHelper.ExecuteNonQuery(sql);
                //}
            }
            catch (Exception)
            {

                throw;
            }
        }

        public class PersonReportMode
        {
            public int owner { get; set; }
            public int crmid { get; set; }
            public string crmlogin { get; set; }
            public long mt5login { get; set; }
            public int scheme_id { get; set; }
            public int level_id { get; set; }
            public decimal coefficient { get; set; }
            public decimal money { get; set; }
            public string date { get; set; }
            public string starttime { get; set; }
            public string endtime { get; set; }
            public string scheme { get; set; }
            public string level { get; set; }
        }

        public static int DelDailyReport(string date)
        {
            try
            {
                string sql = $"delete from daily_report where date='{date}'";
                int count = DBHelper.ExecuteNonQuery(sql);
                return count;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public static void DailyReport(DailyReportMode model)
        {
            try
            {
                int count = 0;
                string sql = $"select * from daily_report where owner={model.owner} and date='{model.date}'";
                DataTable dt = DBHelper.ExecuteQuery(sql);
                if (dt.Rows.Count == 0)
                {
                    sql = $"insert into daily_report(owner,count,money,date,addtime,starttime,endtime) values (" +
                        $"{model.owner},{model.count},{model.money},'{model.date}','{DateTime.Now}','{model.starttime}','{model.endtime}')";
                    count = DBHelper.ExecuteNonQuery(sql);
                }
                //else
                //{
                //    sql = $"update daily_report set owner={model.owner} and date='{model.date}'" +
                //     $" where count={model.count} and money={model.money}";
                //    count = DBHelper.ExecuteNonQuery(sql);
                //}
            }
            catch (Exception)
            {

                throw;
            }
        }

        public class DailyReportMode
        {
            public int owner { get; set; }
            public int count { get; set; }
            public decimal money { get; set; }
            public string date { get; set; }
            public string starttime { get; set; }
            public string endtime { get; set; }
        }

        #endregion

        #region 返佣至钱包

        public static void CommissionToWallet(string orderid, string date, int owner, decimal money)
        {
            try
            {
                //获取钱包信息
                string sql = $"select *from  crm_wallet where crm_id={owner}";
                DataTable dt = DBHelper.ExecuteQuery(sql);
                using (MySqlConnection connection = new MySqlConnection(DBHelper.connectionString))
                {
                    using (MySqlCommand command = new MySqlCommand(sql, connection))
                    {
                        connection.Open();
                        MySqlTransaction transaction = connection.BeginTransaction();
                        try
                        {
                            if (dt.Rows.Count > 0)
                            {
                                #region 钱包流水
                                string crm_wallet_transactions_id = GetOrderNo();
                                DataRow row = dt.Rows[0];
                                string id = row["id"].ToString();
                                decimal before_amount = decimal.Parse(row["balance"].ToString());
                                decimal after_amount = before_amount + money;
                                sql = $"INSERT INTO `crm_wallet_transactions` (`id`, `wallet_id`, `type`, `mode`, `before_amount`, `amount`, `after_amount`, `create_date`, `order_id`, `remark`) " +
                                    $"VALUES ('{crm_wallet_transactions_id}', '{id}', 'cashDeposit', 'rebateDeposit', {before_amount}, {money}, {after_amount}, '{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}', {orderid}, '{date}');";
                                command.CommandText = sql;
                                command.ExecuteNonQuery();
                                #endregion
                                #region 更新余额
                                sql = $"UPDATE crm_wallet SET balance = {after_amount} where  id='{id}'";
                                command.CommandText = sql;
                                command.ExecuteNonQuery();
                                #endregion
                                #region 更新返佣标识
                                sql = $"UPDATE commission_detailed_report SET isreturn = '1' where  order_id='{orderid}'";
                                command.CommandText = sql;
                                command.ExecuteNonQuery();
                                #endregion
                                transaction.Commit();
                            }
                        }
                        catch (Exception ex)
                        {
                            // 回滚事务
                            transaction.Rollback();
                        }
                        finally
                        {
                            connection.Close();
                        }
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
        }

        #region 自动生产时间戳编号

        public static string GetOrderNo(int cot = 5)
        {
            Random random = new Random();
            string strRandom = string.Empty; //生成编号 
            System.Random r = new Random();
            for (int i = 0; i < cot; i++)
            {
                strRandom += r.Next(0, 9);
            }

            string code = GetTimeStamp().ToString() + strRandom;//形如
            return code;
        }

        /// <summary>  
        /// 获取时间戳  13位
        /// </summary>  
        /// <returns></returns>  
        public static long GetTimeStamp()
        {
            TimeSpan ts = DateTime.Now - new DateTime(1970, 1, 1, 0, 0, 0, 0);
            return Convert.ToInt64(ts.TotalSeconds * 1000);
        }

        #endregion

        #endregion
    }
}
